﻿using System.Data;
using System.Configuration;
using MySql.Data.MySqlClient;
using System;

namespace Ama.BugTracker.Web
{
    internal static class UserSql
    {
        internal static DataRow UserLoginCheck(string name, string password)
        {
            const string sql = @"SELECT user.id, user.name, user.isSysAdmin, (COUNT(teamadmin.`teamid`) > 0) AS isteamadmin FROM USER LEFT JOIN teamadmin
                                ON user.id = teamadmin.`userid` WHERE (user.`name` = @name OR user.`usercode` = @name) AND user.`password` = @password AND user.`isStop` = 0";

            using (MySqlConnection conn = AppUtils.CreateDbConnection("BugDbConn"))
            {
                MySqlCommand cmd = new MySqlCommand();
                cmd.Connection = conn;
                cmd.Parameters.AddWithValue("@name", name);
                cmd.Parameters.AddWithValue("@password", password);

                DataRow row = cmd.ExecuteFirstRowSQL(sql);
                return row;
            }
        }

        internal static DataTable QueryAllUserList()
        {
            const string sql = "SELECT id, NAME, usercode FROM USER";
            using (MySqlConnection conn = AppUtils.CreateDbConnection("BugDbConn"))
            {
                MySqlCommand cmd = new MySqlCommand(sql);
                cmd.Connection = conn;

                DataTable table = cmd.ExecuteDataTableSQL(sql);
                return table;
            }
        }
    }
}